*===============================================================================
* Project title		Savings Accounts to Borrow Less	
* PIs			Felipe Kast, Dina Pomeranz 
* File name		2c_tables_analysis_lending.do
*-------------------------------------------------------------------------------
* Description		Creates lending tables 
* Outputs		Tables 4, A11
* Spaces per tab 	8 
*===============================================================================

*===============================================================================
*TOC
*1)	Prepare data for analysis
*2)	Tables
*2.1)	Lending table
*===============================================================================

*===============================================================================
* Section 1 - Prepare data for analysis
*===============================================================================

clear all
global path "~/file_server/project_savings/2_shared/impact/do/November2019"	// Path to processed data 
global results "$path/results"		
cd "$results"									// Change to directory to store results
set more off
set trace off
*Loading processed data
	use $path/datasets/impactDatabase.dta, clear

*===============================================================================
* Section 2 - Tables 
*===============================================================================
* Section 2.1 - Table on lending
*-------------------------------------------------------------------------------
	eststo clear
	
// Calculates content for Panels C-F  
	local i = 6
	
	foreach y in w5_totalLending w5_familyLending w5_businessLending 		/*A7 A8 A9*/		///
			w1_totalLending w1_familyLending w1_businessLending		/*A10 A11 A12*/ 	///
			totalLending familyLending businessLending			/*A13 A14 A15*/ 	///
			IHStotalLending IHSfamilyLending IHSbusinessLending 		/*A16 A17 A18*/	{
	
	local i = `i' + 1 //updates counter
	local j = 1 //tracks column number 
	
	reg d.`y' // Mark subjects nonmissing in both periods
	egen t1 = max(e(sample)), by(idBaseSurvey)
		
		*Col 1 regression. 			
			reghdfe `y' c.accountAccess#c.post post if t1 == 1,	/// Regressing lending outcome variables on treatment x post 
			abs(idBaseSurvey asesor2008#post) vce(cluster groupId)	// Individual FE, Stratum FE x Post, SEs clustered at group level 
			
			gen `y'_s`j' = e(sample) //marks observations used in sample for regression
			
			//adding control mean		
			sum `y' if accountAccess == 0 & post == 1
			if (abs(`r(mean)') < 100) {
				estadd local control_mean = trim("`: display %12.3fc r(mean)'")
			} 
			else {
				estadd local control_mean = trim("`: display %12.0fc r(mean)'")
			}
			
			distinct idBaseSurvey  if e(sample)
			estadd scalar num_ind = r(ndistinct)
			estadd local ind_fixeff = "Yes"
			estadd local stratum_post_fe = "Yes"
			estadd local regret_post = "No"
			estadd local hasacc_post = "No"
			eststo A`i'
		
		*local i = `i' + 1 //updates counter
		local j = `j' + 1 //updates col number
			
		*Col 2 regression. 
			reghdfe `y' c.accountAccess#c.post#c.regretsNotSaving	///
			c.post#c.regretsNotSaving c.accountAccess#c.post post 	///
			if t1 == 1, abs(idBaseSurvey asesor2008#post)		/// 
			vce(cluster groupId) 
			
			gen `y'_s`j' = e(sample) //marks observations used in sample for regression
					
			//adding control mean		
			sum `y' if accountAccess == 0 & post == 1
			if (abs(`r(mean)') < 100) {
				estadd local control_mean = trim("`: display %12.3fc r(mean)'")
			} 
			else {
				estadd local control_mean = trim("`: display %12.0fc r(mean)'")
			}
			
			distinct idBaseSurvey  if e(sample)			// Adding N and FE to estimates stored 
			estadd scalar num_ind = r(ndistinct)
			estadd local ind_fixeff = "Yes"
			estadd local stratum_post_fe = "Yes"
			estadd local regret_post = "Yes"
			estadd local hasacc_post = "No"
			eststo B`i'
		
		*local i = `i' + 1 //updates counter
		*local j = `j' + 1 //updates col number 
			
		drop t1	
		di in red "`i'"
	}

// Variables to add as condition in regression for panels A and B 
	
	local nr = 1
	
	foreach z in totalLending familyLending businessLending{
	foreach x in 1 2 {
	gen in_s`nr'`x' = 1 if w5_`z'_s`x' == 1 & w1_`z'_s`x' == 1 		/// Done to have the same sample used in each regression, for easy use in the next regression loop
	& `z'_s`x' == 1 & IHS`z'_s`x' == 1
	}
	local nr = `nr' + 1
	}
	
	gen in_s41 = in_s11
	gen in_s42 = in_s12
	gen in_s51 = in_s21
	gen in_s52 = in_s22
	gen in_s61 = in_s31
	gen in_s62 = in_s32
	
	local i = 0
	
	local nr = 1

// Calculates content for panels A & B	
	
	foreach y in prLendingTot prLendingfamily prLendingbusiness 		/*A1 A2 A3*/ 	///
		totalLendingIndex familyLendingIndex businessLendingIndex	/*A4 A5 A6*/  {
	
	local i = `i' + 1 //updates counter
	local j = 1 //tracks column number 
	
	
	reg d.`y' //trick to mark subjects nonmissing in both periods
	egen t1 = max(e(sample)), by(idBaseSurvey)
		
		*Col 1 regression. 			
			reghdfe `y' c.accountAccess#c.post post if t1 == 1 & in_s`nr'`j' == 1, /// extra e(sample) restriction added here 
			absorb(idBaseSurvey asesor2008#post) vce(cluster groupId) 
			
			//adding control mean		
			sum `y' if accountAccess == 0 & post == 1
			if (abs(`r(mean)') < 100) {
				estadd local control_mean = trim("`: display %12.3fc r(mean)'")
			} 
			else {
				estadd local control_mean = trim("`: display %12.0fc r(mean)'")
			}
			
			distinct idBaseSurvey  if e(sample)
			estadd scalar num_ind = r(ndistinct)
			estadd local ind_fixeff = "Yes"
			estadd local stratum_post_fe = "Yes"
			estadd local regret_post = "No"
			estadd local hasacc_post = "No"
			eststo A`i'
		
		*local i = `i' + 1 //updates counter
		local j = `j' + 1 //updates col number 
		
			
		*Col 2 regression. 
			reghdfe `y' c.accountAccess#c.post#c.regretsNotSaving			///
			c.post#c.regretsNotSaving c.accountAccess#c.post post 			///
			if t1 == 1 & in_s`nr'`j' == 1, abs(idBaseSurvey asesor2008#post) 	///
			vce(cluster groupId)  
					
			//adding control mean		
			sum `y' if accountAccess == 0 & post == 1
			if (abs(`r(mean)') < 100) {
				estadd local control_mean = trim("`: display %12.3fc r(mean)'")
			} 
			else {
				estadd local control_mean = trim("`: display %12.0fc r(mean)'")
			}
			
			distinct idBaseSurvey  if e(sample)
			estadd scalar num_ind = r(ndistinct)
			estadd local ind_fixeff = "Yes"
			estadd local stratum_post_fe = "Yes"
			estadd local regret_post = "Yes"
			estadd local hasacc_post = "No"
			eststo B`i'
		
		*local i = `i' + 1 //updates counter	
		*local j = `j' + 1 //updates col number 
		local nr = `nr' + 1
			
		drop t1	
		di in red "`i'"
	}
	
	
*-------------------------------------------------------------------------------	
*2.1.2 Table code:
*-------------------------------------------------------------------------------
	
* Appendix table with 6 panels 

//probability	
	esttab A1 A2 A3 B1 B2 B3 using tab_lend_6col.tex, replace				///
	cells(b(star fmt(%12.3fc)) se(par fmt(%12.3fc))) label style(tex) nonumber	///
	stats( control_mean, fmt(%12.3fc %12.0fc %12.0fc)				///
	label("Control mean")) mlabels(,none) 						///	
	collabels(, none) eqlabels(, none)  						///
	keep(c.accountAccess#c.post#c.regretsNotSaving 					///
		c.accountAccess#c.post)							///
	order(c.accountAccess#c.post#c.regretsNotSaving 				///
		c.accountAccess#c.post) 						///
	varlabels(c.accountAccess#c.post#c.regretsNotSaving 				///
		"Account $\times$ post $\times$ (baseline: regret not saving more)"	///
		c.accountAccess#c.post "Account $\times$ post")				///
	starlevels(* 0.1 ** 0.05 *** 0.01) 						///
	prehead(\begin{tabular}{l*{@M}{c}} \hline \hline) 				///	
	posthead(	& (1) 		& (2) 		& (3) 		& (4)		& (5)		& (6)		\\ 	///
			&  Total  	& Lent to 	& Lent to 	&  Total  	& Lent to 	& Lent to	\\ 	///
			&  lending	& family and 	& business 	&  lending	& family and 	& business 	\\	///
			& 		& friends 	& contacts 	& 		& friends 	& contacts	\\ 	///
		\hline \\ \multicolumn{@span}{l}{\textbf{Panel A:} Probability of Any Lending} \\ ) 		///
	prefoot( ) 												///
	postfoot( )

//categories
	esttab A4 A5 A6 B4 B5 B6 using tab_lend_6col.tex, append			///
	cells(b(star fmt(%12.3fc)) se(par fmt(%12.3fc))) label style(tex) nonumber	///
	stats( control_mean, fmt(%12.3fc %12.0fc %12.0fc)				///
	label("Control mean")) mlabels(,none) 						///	
	collabels(, none) eqlabels(, none)  						///
	keep(c.accountAccess#c.post#c.regretsNotSaving 					///
		c.accountAccess#c.post)							///
	order(c.accountAccess#c.post#c.regretsNotSaving 				///
		c.accountAccess#c.post) 						///
	varlabels(c.accountAccess#c.post#c.regretsNotSaving 				///
		"Account $\times$ post $\times$ (baseline: regret not saving more)"	///
		c.accountAccess#c.post "Account $\times$ post")				///
	starlevels(* 0.1 ** 0.05 *** 0.01) 						///
	prehead( )									///
	posthead(\\ \multicolumn{@span}{l}{\textbf{Panel B:} Categories of Lending} \\) ///
	prefoot( ) postfoot( )
	
//winsor 5%
	esttab A7 A8 A9 B7 B8 B9 using tab_lend_6col.tex, append			///
	cells(b(star fmt(%12.0fc)) se(par fmt(%12.0fc))) label style(tex) nonumber	///
	stats( control_mean, fmt(%12.3fc %12.0fc %12.0fc)				///
	label("Control mean")) mlabels(,none) 						///	
	collabels(, none) eqlabels(, none)  						///
	keep(c.accountAccess#c.post#c.regretsNotSaving 					///
		c.accountAccess#c.post)							///
	order(c.accountAccess#c.post#c.regretsNotSaving 				///
		c.accountAccess#c.post) 						///
	varlabels(c.accountAccess#c.post#c.regretsNotSaving 				///
		"Account $\times$ post $\times$ (baseline: regret not saving more)"	///
		c.accountAccess#c.post "Account $\times$ post")				///
	starlevels(* 0.1 ** 0.05 *** 0.01) 						///
	prehead( )									///
	posthead(\\ \multicolumn{@span}{l}{\textbf{Panel C:}  Amounts (Winsorized at the Top 5\%)} \\) ///
	prefoot( ) postfoot( )

//winsor 1%
	esttab A10 A11 A12 B10 B11 B12 using tab_lend_6col.tex, append			///
	cells(b(star fmt(%12.0fc)) se(par fmt(%12.0fc))) label style(tex) nonumber	///
	stats( control_mean, fmt(%12.3fc %12.0fc %12.0fc)				///
	label("Control mean")) mlabels(,none) 						///	
	collabels(, none) eqlabels(, none)  						///
	keep(c.accountAccess#c.post#c.regretsNotSaving 					///
		c.accountAccess#c.post)							///
	order(c.accountAccess#c.post#c.regretsNotSaving 				///
		c.accountAccess#c.post) 						///
	varlabels(c.accountAccess#c.post#c.regretsNotSaving 				///
		"Account $\times$ post $\times$ (baseline: regret not saving more)"	///
		c.accountAccess#c.post "Account $\times$ post")				///
	starlevels(* 0.1 ** 0.05 *** 0.01) 						///
	prehead( )									///
	posthead(\\ \multicolumn{@span}{l}{\textbf{Panel D:}  Amounts (Winsorized at the Top 1\%)} \\) ///
	prefoot( ) postfoot( )
	
// non-winsorized
	esttab A13 A14 A15 B13 B14 B15 using tab_lend_6col.tex, append			///
	cells(b(star fmt(%12.0fc)) se(par fmt(%12.0fc))) label style(tex) nonumber	///
	stats( control_mean, fmt(%12.3fc %12.0fc %12.0fc)				///
	label("Control mean")) mlabels(,none) 						///	
	collabels(, none) eqlabels(, none)  						///
	keep(c.accountAccess#c.post#c.regretsNotSaving 					///
		c.accountAccess#c.post)							///
	order(c.accountAccess#c.post#c.regretsNotSaving 				///
		c.accountAccess#c.post) 						///
	varlabels(c.accountAccess#c.post#c.regretsNotSaving 				///
		"Account $\times$ post $\times$ (baseline: regret not saving more)"	///
		c.accountAccess#c.post "Account $\times$ post")				///
	starlevels(* 0.1 ** 0.05 *** 0.01) 						///
	prehead( )									///
	posthead(\\ \multicolumn{@span}{l}{\textbf{Panel E:} Non-Winsorized Amounts} \\) ///
	prefoot( ) postfoot( )	
	
// IHS
	esttab A16 A17 A18 B16 B17 B18 using tab_lend_6col.tex, append			///
	cells(b(star fmt(%12.3fc)) se(par fmt(%12.3fc))) label style(tex) nonumber	///
	stats( control_mean num_ind N regret_post ind_fixeff stratum_post_fe, fmt(%12.3fc %12.0fc %12.0fc)		///
	label("Control mean" "\\ \hline Individuals" "Observations" "Post $\times$ (baseline: regret not saving more)" 	///
		"Individual FE" "Stratum FE $\times$ post" )) mlabels(,none) 		///	
	collabels(, none) eqlabels(, none)  						///
	keep(c.accountAccess#c.post#c.regretsNotSaving 					///
		c.accountAccess#c.post)							///
	order(c.accountAccess#c.post#c.regretsNotSaving 				///
		c.accountAccess#c.post) 						///
	varlabels(c.accountAccess#c.post#c.regretsNotSaving 				///
		"Account $\times$ post $\times$ (baseline: regret not saving more)"	///
		c.accountAccess#c.post "Account $\times$ post")				///
	prehead( ) ///
	posthead(\\ \multicolumn{@span}{l}{\textbf{Panel F:} Inverse Hyperbolic Sine of Amount} \\) ///
	prefoot( ) ///
	postfoot(\hline \hline \end{tabular} )		
	
* Main table with 3 panels 

//probability	
	esttab A1 A2 A3 B1 B2 B3 using tab_lend_6col_main.tex, replace				///
	cells(b(star fmt(%12.3fc)) se(par fmt(%12.3fc))) label style(tex) nonumber	///
	stats( control_mean, fmt(%12.3fc %12.0fc %12.0fc)				///
	label("Control mean")) mlabels(,none) 						///	
	collabels(, none) eqlabels(, none)  						///
	keep(c.accountAccess#c.post#c.regretsNotSaving 					///
		c.accountAccess#c.post)							///
	order(c.accountAccess#c.post#c.regretsNotSaving 				///
		c.accountAccess#c.post) 						///
	varlabels(c.accountAccess#c.post#c.regretsNotSaving 				///
		"Account $\times$ post $\times$ (baseline: regret not saving more)"	///
		c.accountAccess#c.post "Account $\times$ post")				///
	starlevels(* 0.1 ** 0.05 *** 0.01) 						///
	prehead(\begin{tabular}{l*{@M}{c}} \hline \hline) 				///	
	posthead(	& (1) 		& (2) 		& (3) 		& (4)		& (5)		& (6)		\\ 	///
			&  Total  	& Lent to 	& Lent to 	&  Total  	& Lent to 	& Lent to	\\ 	///
			&  lending	& family and 	& business 	&  lending	& family and 	& business 	\\	///
			& 		& friends 	& contacts 	& 		& friends 	& contacts	\\ 	///
		\hline \\ \multicolumn{@span}{l}{\textbf{Panel A:} Probability of Any Lending} \\ ) 		///
	prefoot( ) 												///
	postfoot( )
	
//winsor 5%
	esttab A7 A8 A9 B7 B8 B9 using tab_lend_6col_main.tex, append			///
	cells(b(star fmt(%12.0fc)) se(par fmt(%12.0fc))) label style(tex) nonumber	///
	stats( control_mean, fmt(%12.3fc %12.0fc %12.0fc)				///
	label("Control mean")) mlabels(,none) 						///	
	collabels(, none) eqlabels(, none)  						///
	keep(c.accountAccess#c.post#c.regretsNotSaving 					///
		c.accountAccess#c.post)							///
	order(c.accountAccess#c.post#c.regretsNotSaving 				///
		c.accountAccess#c.post) 						///
	varlabels(c.accountAccess#c.post#c.regretsNotSaving 				///
		"Account $\times$ post $\times$ (baseline: regret not saving more)"	///
		c.accountAccess#c.post "Account $\times$ post")				///
	starlevels(* 0.1 ** 0.05 *** 0.01) 						///
	prehead( )									///
	posthead(\\ \multicolumn{@span}{l}{\textbf{Panel B:}  Amounts (Winsorized at the Top 5\%)} \\) ///
	prefoot( ) postfoot( )
	
// IHS
	esttab A16 A17 A18 B16 B17 B18 using tab_lend_6col_main.tex, append			///
	cells(b(star fmt(%12.3fc)) se(par fmt(%12.3fc))) label style(tex) nonumber	///
	stats( control_mean num_ind N regret_post ind_fixeff stratum_post_fe, fmt(%12.3fc %12.0fc %12.0fc)		///
	label("Control mean" "\\ \hline Individuals" "Observations" "Post $\times$ (baseline: regret not saving more)" 	///
		"Individual FE" "Stratum FE $\times$ post" )) mlabels(,none) 		///	
	collabels(, none) eqlabels(, none)  						///
	keep(c.accountAccess#c.post#c.regretsNotSaving 					///
		c.accountAccess#c.post)							///
	order(c.accountAccess#c.post#c.regretsNotSaving 				///
		c.accountAccess#c.post) 						///
	varlabels(c.accountAccess#c.post#c.regretsNotSaving 				///
		"Account $\times$ post $\times$ (baseline: regret not saving more)"	///
		c.accountAccess#c.post "Account $\times$ post")				///
	prehead( ) ///
	posthead(\\ \multicolumn{@span}{l}{\textbf{Panel C:} Inverse Hyperbolic Sine of Amount} \\) ///
	prefoot( ) ///
	postfoot(\hline \hline \end{tabular} )	
	
